﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Sida.Common;


namespace AccessControl.Sida.DataAccess
{
    public class SidaInfoAccess
    {


        public static DataTable SelectTerms()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(@"SELECT     name,SUBSTRING(name, 2, 5) as TermNo, SUBSTRING(name, 2, 2)+SUBSTRING(name, 6, 1) AS Vorood, CASE WHEN SUBSTRING(name, 6, 1) = '1' THEN 'نیمسال اول       ' WHEN SUBSTRING(name, 6, 1) = '2' THEN 'نیمسال دوم      ' WHEN SUBSTRING(name, 6, 1)= '3' THEN 'دوره ی تابستان  ' END + SUBSTRING(name, 4, 2) + '-' + SUBSTRING(name, 2, 2) AS TermPersianCaption
FROM        master.dbo.sysdatabases
WHERE     (name LIKE 't%') AND (LEN(name) = 6) AND (SUBSTRING(name, 6, 1) IN ('1', '2', '3')) AND (ISNUMERIC(SUBSTRING(name, 2, 5)) = 1) ORDER BY TermNo Desc", cn);
                cn.Open();
                var adp = new SqlDataAdapter(cmdSelect);
                var tbl = new DataTable();
                adp.Fill(tbl);
                cn.Close();
                return tbl;
            }
        }

        public static DataTable SelectBranches()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand( "SELECT id,nameresh,idgroup,namegroup FROM fresh ORDER BY nameresh", cn);
                cn.Open();
                var adp=new SqlDataAdapter(cmdSelect);
                var tbl=new DataTable();
                adp.Fill(tbl);
                cn.Close();
                return tbl;
            }
        }

    }
}
